package day2.Exer530;

import day1.Exer.DBUtil;
import day2.Subject;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    public int add(Student s){
        List<Object> list=new ArrayList<>();
        list.add(s.getSname());
        list.add(s.getSex());
        list.add(s.getTelephone());
        list.add(s.getQq());
        list.add(s.getWechat());
        list.add(s.getGradeid());
        list.add(Date.valueOf(s.getEnterday()));
        list.add(s.getFromcity());
        list.add(Date.valueOf(s.getBirthday()));
        list.add(s.getAddress());
        list.add(s.getPass());
        list.add(s.getIdcardpic());
        String sql="insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterday,fromcity,birthday,address,pass,idcardpic)" +
                "values(?,?,?,?,?,?,?,?,?,?,?,?)";
        return DBUtil.executeUpdate(sql,list);
    }
    public int update(Student s){
        List<Object> list=new ArrayList<>();
        list.add(s.getSname());
        list.add(s.getSex());
        list.add(s.getTelephone());
        list.add(s.getQq());
        list.add(s.getWechat());
        list.add(s.getGradeid());
        list.add(Date.valueOf(s.getEnterday()));
        list.add(s.getFromcity());
        list.add(Date.valueOf(s.getBirthday()));
        list.add(s.getAddress());
        list.add(s.getPass());
        list.add(s.getIdcardpic());
        String sql = "UPDATE s_studen set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterday=?,fromcity=?,birthday=?,address=?,pass=?,idcardpic=? where stuno=" + s.getStuno();
        return DBUtil.executeUpdate(sql,list);
    }
    public int delete(long id){
        List<Object> list=new ArrayList<>();
        list.add(id);
        String sql="delete from s_student where id=?";
        return DBUtil.executeUpdate(sql,list);
    }
    public Student get(int stuno){
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        Student s=null;
        try {
            connection=DBUtil.getConnection();
            String sql="select *from s_student where stuno="+stuno;
            st=connection.prepareStatement(sql);
            rs=st.executeQuery();
            if(rs.next()){
                s=new Student(rs.getInt("stuno"),rs.getString("sname"),rs.getString("sex"),
                rs.getString("telephone"),rs.getString("qq"),rs.getString("wechat"),rs.getLong("gradeid"),
                rs.getString("enterday"),rs.getString("fromcity"),rs.getString("birthday"),rs.getString("address"),
                rs.getString("pass"),rs.getString("idcardpic"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }
    public List<Student> findAll(){
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        List<Student> list =new ArrayList<>();
        try {
            connection=DBUtil.getConnection();
            String sql="select *from s_student";
            st=connection.prepareStatement(sql);
            rs=st.executeQuery();
            while(rs.next()){
                list.add(new Student(rs.getInt("stuno"),rs.getString("sname"),rs.getString("sex"),
                        rs.getString("telephone"),rs.getString("qq"),rs.getString("wechat"),rs.getLong("gradeid"),
                        rs.getString("enterday"),rs.getString("fromcity"),rs.getString("birthday"),rs.getString("address"),
                        rs.getString("pass"),rs.getString("idcardpic")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public List<Student> search(int pageNo,int pageSize){
        List<Student> studentList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from s_student order by id desc limit ?,?";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setInt(1, (pageNo - 1) * pageSize);
            st.setInt(2, pageSize);
            rs = st.executeQuery();
            while (rs.next()) {
                studentList.add(new Student(rs.getInt("stuno"),rs.getString("sname"),rs.getString("sex"),
                        rs.getString("telephone"),rs.getString("qq"),rs.getString("wechat"),rs.getLong("gradeid"),
                        rs.getString("enterday"),rs.getString("fromcity"),rs.getString("birthday"),rs.getString("address"),
                        rs.getString("pass"),rs.getString("idcardpic")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, connection);
        }
        return studentList;
    }
}
